package cn.calm.repaymentNotice.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteHelper extends SQLiteOpenHelper {
	private static final int VERSION=1;
	private static final String DBNAME="repayment_notice.db";
	public SQLiteHelper(Context context) {
		super(context, DBNAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//创建提醒记录表
		db.execSQL("CREATE TABLE REPAYMENT_NOTICE(ID INTEGER PRIMARY KEY AUTOINCREMENT ,NAME VARCHAR(30) ,NOTICE_TYPE INTEGER,REPAYMENT_DATE INTEGER,PRE_NOTICE_DAYS INTEGER,YEAR INTEGER)");
		//创建信用卡还款记录表
		db.execSQL("CREATE TABLE CREDIT_CARD_RECORD(REPAYMENT_NOTICE_ID INTEGER,PREDICT_REPAYMENT_DATE DATE ,REPAYMENT_DATE DATE,RESULT INTEGER,COMMENT TEXT)");
		//创建还款计划表
		db.execSQL("CREATE TABLE CREDIT_PLAN_RECORD(REPAYMENT_NOTICE_ID INTEGER,PREDICT_REPAYMENT_DATE DATE ,REPAYMENT_DATE DATE,RESULT INTEGER,COMMENT TEXT,PREDICT_REpAYMENT_AMOUNT REAL,REPAYMENT_AMOUNT REAL)");
		//创建当前还款计划View
		db.execSQL("CREATE VIEW \"CURRENT_CREDIT_PLAN\" AS SELECT CPT.REPAYMENT_NOTICE_ID, MIN(CPT.PREDICT_REPAYMENT_DATE) PREDICT_REPAYMENT_DATE, CPT.REPAYMENT_DATE,CPT.RESULT,CPT.COMMENT,CPT.PREDICT_REpAYMENT_AMOUNT,CPT.REPAYMENT_AMOUNT FROM	( SELECT DISTINCT T.* FROM CREDIT_PLAN_RECORD T	WHERE T.REPAYMENT_DATE IS NULL ) CPT GROUP BY CPT.REPAYMENT_NOTICE_ID");
		//创建还款提醒view
		db.execSQL("CREATE VIEW \"REPAYMENT_NOTICE_INFO\" AS SELECT RN.ID, RN.NAME, RN.NOTICE_TYPE, RN.PRE_NOTICE_DAYS, CASE WHEN RN.NOTICE_TYPE = 0 THEN CR.PREDICT_REPAYMENT_DATE WHEN RN.NOTICE_TYPE = 1 THEN CP.PREDICT_REPAYMENT_DATE END PREDICT_REPAYMENT_DATE FROM REPAYMENT_NOTICE RN LEFT JOIN CREDIT_CARD_RECORD CR ON RN.ID = CR.REPAYMENT_NOTICE_ID AND CR.REPAYMENT_DATE IS NULL LEFT JOIN CURRENT_CREDIT_PLAN CP ON RN.ID = CP.REPAYMENT_NOTICE_ID");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}
}
